package cn.com.petrochina.lpm3.util;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtil {
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<Map<String, Object>> queryForList(String sql, Object[] params) {
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql, params);
		 /*
		 for(Map map : list){
			 Set<Entry> set = map.entrySet();
			 for(Entry o : set){
				 //System.out.print(o.getKey()+"::"+o.getValue()+",");
			 }
			 System.out.println();
		 }
		 */
        return list;
    }

    public void updateForList(String sql, Object[] params) {
        this.jdbcTemplate.update(sql, params);
		 /*
		 for(Map map : list){
			 Set<Entry> set = map.entrySet();
			 for(Entry o : set){
				 //System.out.print(o.getKey()+"::"+o.getValue()+",");
			 }
			 System.out.println();
		 }
		 */

    }

    public List<Map<String, Object>> queryForList(String sql) {
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
		 /*
		 for(Map map : list){
			 Set<Entry> set = map.entrySet();
			 for(Entry o : set){
				 //System.out.print(o.getKey()+"::"+o.getValue()+",");
			 }
			 System.out.println();
		 }
		 */
        return list;
    }

    public int[] batchUpdate(String[] sql) {
        int[] aa = jdbcTemplate.batchUpdate(sql);
        return aa;
    }
//	public int queryForInt(String sql){
//		return jdbcTemplate.queryForInt(sql);
//	}

    /**
     * 批量更新、插入
     *
     * @param sql  批处理预编译sql
     * @param data 参数集合
     */
    public void executeBatchUpdate(final String sql, final List<List<Object>> data) {
        if (data == null || data.size() == 0) return;
        System.out.println("executeBatchUpdate sql:" + sql);
        jdbcTemplate.execute(new ConnectionCallback() {
            public Object doInConnection(Connection conn) throws SQLException,
                    DataAccessException {
                PreparedStatement p = conn.prepareStatement(sql);
                for (int j = 0; j < data.size(); j++) {
                    for (int i = 0; i < data.get(j).size(); i++) {
                        Object o = data.get(j).get(i);
                        System.out.print(o + ",");
                        if (o instanceof java.util.Date) {
                            java.sql.Date sqlDate = new java.sql.Date(((java.util.Date) o).getTime());
                            p.setDate(i + 1, sqlDate);
                            continue;
                        }
                        p.setObject(i + 1, o);
                    }
                    System.out.println("");
                    p.executeUpdate();
                }
                return null;
            }
        });
    }

    public void executeSql(String sql) {
        jdbcTemplate.execute(sql);
    }

    public void updateSql(String sql, Object[] args) {
        jdbcTemplate.update(sql, args);
    }

    /**
     * 查询表数据
     *
     * @param sql
     * @param params
     * @return
     */
    public List<Map<String, Object>> selectTableForColumnType(String sql, Object[] params) {
        final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        jdbcTemplate.query(sql, params,
                new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        Map<String, Object> oo = new HashMap<String, Object>();
                        ResultSetMetaData rss = rs.getMetaData();
                        int columnCount = rss.getColumnCount();
                        System.out.println("columnCount:" + columnCount);
                        for (int i = 1; i <= columnCount; i++) {
                            String columnTypeName = rss.getColumnTypeName(i);
                            String columnLabel = rss.getColumnLabel(i);
                            Object o = SqlUtil.getColumnValue(rs, columnTypeName, columnLabel);
                            oo.put(rss.getColumnLabel(i), o);
                            //System.out.print(rss.getColumnLabel(i)+":"+ rs.getString(rss.getColumnLabel(i))+" , ");
                            // System.out.println(rss.getColumnType(i)+","+rss.getColumnTypeName(i));
                        }
                        //System.out.println("\n=====================");
                        list.add(oo);
                    }
                }
        );
        return list;
    }

    //增加的方法
    public List<Map<String, Object>> selectTableForColumnType(String sql) {
        final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        jdbcTemplate.query(sql,
                new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        long endData = System.currentTimeMillis();
                        System.out.println(endData);
                        Map<String, Object> oo = new HashMap<String, Object>();
                        ResultSetMetaData rss = rs.getMetaData();
                        int columnCount = rss.getColumnCount();
                        //  System.out.println("columnCount:"+columnCount);
                        for (int i = 1; i <= columnCount; i++) {
                            String columnTypeName = rss.getColumnTypeName(i);
                            String columnLabel = rss.getColumnLabel(i);
                            Object o = SqlUtil.getColumnValue(rs, columnTypeName, columnLabel);
                            oo.put(rss.getColumnLabel(i), o);
                            //System.out.print(rss.getColumnLabel(i)+":"+ rs.getString(rss.getColumnLabel(i))+" , ");
                            // System.out.println(rss.getColumnType(i)+","+rss.getColumnTypeName(i));
                        }
                        //System.out.println("\n=====================");
                        list.add(oo);
                    }
                }
        );

        return list;
    }

}
